const express = require('express');
const router = express.Router();
// 导入数据库
const db = require('../config/database');

router.get('/list', (req, res) => {
    const {pagination,params} = req.query;

    
    let sql = `SELECT 
        q.id,
        q.title,
        q.content,
        q.like_content,
        q.subject,
        kt.title AS subject_name,
        q.views,
        q.difficulty,
        -- 使用CASE语句根据difficulty字段的值进行转换
        CASE 
            WHEN q.difficulty BETWEEN 1 AND 2 THEN '简单'
            WHEN q.difficulty BETWEEN 3 AND 4 THEN '一般'
            WHEN q.difficulty BETWEEN 5 AND 6 THEN '困难'
        END AS difficulty_name
    FROM
        tb_question q
    -- 左连接tb_knowledge_type表，通过subject字段关联
    LEFT JOIN
        tb_knowledge_type kt ON q.subject = kt.id`;

    let values = [];
    if (params.type_id > 0) {
        sql += " WHERE q.subject =?";
        values.push(params.type_id);
    }

    // 根据difficulty参数构建排序语句
    if (params.difficulty === 'true') {
        sql += " ORDER BY q.difficulty ASC";
    } else if (params.difficulty === 'false') {
        sql += " ORDER BY q.difficulty DESC";
    }

    // 根据views参数构建排序语句
    if (params.views === 'true') {
        sql += " ORDER BY q.views ASC";
    } else if (params.views === 'false') {
        sql += " ORDER BY q.views DESC";
    }

    // 如果difficulty和views都没传具体排序要求，默认按照id降序排列
    if (!params.difficulty &&!params.views) {
        sql += " ORDER BY q.id DESC";
    }

    // 计算分页的偏移量（从哪条记录开始取）
    const offset = (pagination.currentPage - 1) * pagination.pageCount;
    // 添加分页语句，使用LIMIT关键字，第一个参数是偏移量，第二个参数是每页数量
    sql += ` LIMIT ${offset}, ${pagination.pageCount}`;
    

    // 查询数据库
    db.query(sql, values, (err, result) => {
        if (err) throw err;
        res.json(result);
    });
});

module.exports = router